Embodiments of the invention are directed to techniques for maintaining data integrity in a relational database management system (RDBMS). More specifically, embodiments of the invention provide a method for managing an index used to enforce a temporal key property in a RDBMS table.
A RDBMS supports the declaration and automatic enforcement of keys for a table. RDBMS applications, such as a database management tool, enforce keys to help maintain the integrity of data in the RDBMS and to reduce application complexity. A unique key is a column or a set of columns in a table for which any specific value or combination of values in the column or set of columns appears in at most one row of the table. In other words, considering only the key column or set of columns, the specific value or combination of values therein are unique to a row of the table. Enforcing a key ensures that the specific values (or combination of values) of a key column (or set of columns) remain distinct following deletions, insertions, and updates of table rows.
A temporal key adds a time dimension to a database key. The time dimension associates a start and an end time with a database key (or multi-column key). The time dimension allows the same key to occur in a database table, so long as the time dimensions do not overlap. If a temporal key is defined for a table column or set of columns, then any specific value or combination of values in the column or set of columns appears in no more than one row of the table at any given point in time. In other words, considering only the temporal key column or set of columns, the values or combination of values therein are unique to a row of the table at any given point in time.
A database management tool may enforce a temporal key property by maintaining an index on a temporal key column (or a set of columns) in a database table. The database management tool may consult and manage entries in the index in response to database table requests.